LIKE Operator

The like operator is used to match string values in where. You can fetch records that contain specific string characters.

For Example:

Let's write a query to fetch employees records whose first_name starts with the letter A followed by any other character or numbers.

select * from employees where first_name like 'A%';

Result:

ilike

like is case-sensitive. ilike is case-insensitive. This means if you use like 'A%' it will only return records whose first_name starts with A, not the small letter a. To also get the records starting with a, use ilike.

select * from employees where first_name ilike 'a%';

Result:

Ecto query for like

like/2

Expression

In Ecto.Query you can do it with the like/2 function from Ecto.Query.

For Example:

HR.Employee
|> where([c], like(c.first_name, "A%"))
|> HR.Repo.all()

On the above code, like(c.first_name, "A&") will search for employees whose first_name starts with A followed by any characters or numbers. Here [c] is the reference variable that is used to refer to HR.Employee. Refer Aliases in Ecto to learn about aliases/reference variables.

ilike/2

HR.Employee
|> where([c], ilike(c.first_name, "a%"))
|> HR.Repo.all()

The above code will search for employees whose first_name starts with a and A.

IEx(27)> HR.Employee |> where([c], ilike(c.first_name, "a%")) |> HR.Repo.all()

[
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 103,
    first_name: "Alexander",
    last_name: "Hunold",
    email: "alexander.hunold@sqltutorial.org",
    phone_number: "590.423.4567",
    hire_date: ~D[1990-01-03],
    salary: Decimal.new("9000.00"),
    manager_id: 102,
    job_id: 9,
    department_id: 6
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 115,
    first_name: "Alexander",
    last_name: "Khoo",
    email: "alexander.khoo@sqltutorial.org",
    phone_number: "515.127.4562",
    hire_date: ~D[1995-05-18],
    salary: Decimal.new("3100.00"),
    manager_id: 114,
    job_id: 13,
    department_id: 3
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 121,
    first_name: "Adam",
    last_name: "Fripp",
    email: "adam.fripp@sqltutorial.org",
    phone_number: "650.123.2234",
    hire_date: ~D[1997-04-10],
    salary: Decimal.new("8200.00"),
    manager_id: 100,
    job_id: 19,
    department_id: 5
  }
]

Keywords

HR.Repo.all(from c in HR.Employee, where: like(c.first_name, "A%"))

where is a keyword accepting a function as a value called like/2.

Back to Homepage